from datetime import datetime

import mysql.connector
from mysql.connector import Error


class SqlHelper:
    __instance = None

    @staticmethod
    def get_instance():
        """ 静态方法，用于获取类的实例 """
        if SqlHelper.__instance is None:
            SqlHelper()
        return SqlHelper.__instance

    def __init__(self):
        """ 私有构造函数，防止外部直接实例化 """
        if SqlHelper.__instance is not None:
            raise Exception("Use get_instance() method to get the singleton instance of this class.")
        else:
            SqlHelper.__instance = self
            # 这里设置你的数据库连接参数  
            self.config = {
                'host': 'localhost',
                'user': 'root',
                'password': '123',
                'database': 'z_sproot_series'
            }
            self.connection = None
            self.connect()

    def connect(self):
        """ 创建数据库连接 """
        try:
            self.connection = mysql.connector.connect(**self.config)
            if self.connection.is_connected():
                db_info = self.connection.get_server_info()
                print("Connected to MySQL Server version ", db_info)
        except Error as e:
            print(f"Error: '{e}'")

    def close(self):
        """ 关闭数据库连接 """
        if self.connection.is_connected():
            self.connection.close()
            print('MySQL connection is closed')

    def execute_query(self, query):
        """ 执行查询并返回结果 """
        print('Executing query')
        cursor = self.connection.cursor()
        try:
            cursor.execute(query)
            # self.connection.commit()
            return cursor.fetchall()
        except Error as e:
            print(f"Error: '{e}'")
            self.connection.rollback()
        finally:
            cursor.close()

    def execute_curd(self, query, val=None):
        """ 执行查询并返回结果 """
        print('Executing query')
        cursor = self.connection.cursor()
        try:
            cursor.execute(query, val)
            self.connection.commit()
            print('Command executed successfully')
        except Error as e:
            print(f"Error: '{e}'")
            self.connection.rollback()
        finally:
            cursor.close()
        # 使用示例

    def call_proc(self, proc_name, args):
        """ 调用存储过程 """
        cursor = self.connection.cursor()
        try:
            cursor.callproc(proc_name,args)
            self.connection.commit()
            # return cursor.fetchall()
        except Error as e:
            print(f"Error: '{e}'")
            self.connection.rollback()
        finally:
            cursor.close()


if __name__ == '__main__':
    # 查询ok
    results = SqlHelper.get_instance().execute_query("SELECT id,calendar,week FROM z_trade_calendar where is_trade_day = 0;")
    # print(results)
    for result in results:
        today = datetime.today().date()
        if today == result[1]:
            print(result)

    # 插入 ok
    # results = SqlHelper.get_instance().execute_curd(" insert into a_student (id ,name ) values (2,'cychan')")

    # proc ok
    # results = SqlHelper.get_instance().call_proc('proc_test', [])
    # print(results)
    # 结束时关闭连接（通常在应用关闭时调用）
    SqlHelper.get_instance().close()
